const mysql = require('mysql2/promise'),
      { con, ResData } = require('./common');
      
/*根据课程类型查询课程*/
async function getCourseByClaid(cla_id = 1){
  let res = new ResData();
  let sql = `select course_id, course_name, cla_name, tag_name, course_img, level_name, course_time
    from courses, classification, tags, level
    where course_tag = tag_id and course_level = level_id and cla_id = classification and cla_id = ${cla_id};`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
};
/*分页查询课程*/
async function getCourseByPage(page, limit){
  let res = new ResData();
  let sql = `select course_id, course_name, cla_name, tag_name, course_img, level_name, course_time
    from courses, classification, tags, level
    where course_tag = tag_id and course_level = level_id and cla_id = classification
    order by course_id
    limit ${limit*(page-1)},${limit};`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
};
/*根据用户id查询用户课程*/
async function getCourseByUserid(user_id){
  let res = new ResData();
  let sql = `select user_id, courses.course_id, course_name, cla_name, tag_name, course_img, level_name, course_time
    from courses, classification, tags, level, usercourses
    where course_tag = tag_id and course_level = level_id and cla_id = classification and courses.course_id=usercourses.course_id and user_id = ${user_id};`;
  var [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
};
/*根据课程id查询子课程*/
async function getChildcourseByCourseid(course_id){
  let res = new ResData();
  let sql = `select childcourses.course_id,course_name, cc_id, cc_name, cc_src, cc_time, level_name
    from courses, childcourses, level
    where course_level = level_id and childcourses.course_id = courses.course_id and childcourses.course_id = ${course_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*根据课程id查询课程*/
async function getCourseByCourseid(course_id){
  let res = new ResData();
  let sql = `select course_id, course_name, cla_name, tag_name, course_img, level_name, course_time
    from courses, classification, tags, level
    where course_tag = tag_id and course_level = level_id and cla_id = classification and course_id = ${course_id};`;
  let [rows] = await con.execute(sql);
  res.data = rows;
  res.count = rows.length;
  return res;
}
/*删除课程*/
async function delCourseByCourseid(course_id){
  let res = new ResData();
  let sql = `delete from courses where course_id=${course_id};`;
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401002;
    res.msg = 'delete failed';
  }
  return res;
};
/*获取course_id*/
async function getCourseid(){
  let res = new ResData();
  let sql1 = `delete from courses
    where course_name is null and classification is null and course_img is null and course_tag is null and course_time is null;`;
  let sql2 = `insert into courses() values ();`;
  let sql3 = `select course_id from courses
where course_name is null and classification is null and course_img is null and course_tag is null and course_time is null;`
  try{
    await con.execute(sql1);
    await con.execute(sql2);
    let [rows] = await con.execute(sql3);
    let course_id = rows[0].course_id;
    res.data = course_id;
  }catch(e){
    res.code = 401001;
    res.msg = 'getcourseid failed';
  }
  return res;
}
/*上传课程*/
async function addCourse(course_id,course_name,classification, course_tag,course_level,course_time,course_img){
  let res = new ResData();
  let sql = `insert into courses(course_id,course_name, classification, course_tag,course_level,course_time,course_img)
values(${course_id},'${course_name}', '${classification}', '${course_tag}','${course_level}','${course_time}','${course_img}');`;
  console.log(sql);
  try{
    [rows] = await con.execute(sql);
    res.data = 'ok';
  }catch(e){
    res.code = 401001;
    res.msg = 'insert failed';
  }
  return res;
}
/*上传课程封面*/
async function addCourseimg(course_id,course_img){
  let res = new ResData();
  let sql = `update courses set course_img='${course_img}'
where course_id = ${course_id};`;
  console.log(sql);
  try{
    [rows] = await con.execute(sql);
    res.data = 'ok';
  }catch(e){
    res.code = 401001;
    res.msg = 'insert failed';
  }
  return res;
}
/*删除子课程*/
async function delChildcourse(course_id,cc_id){
  let res = new ResData();
  let sql = `delete from childcourses
    where course_id=${course_id} and cc_id=${cc_id};`;
  console.log(sql)
  try {
    var [rows] = await con.execute(sql);
  } catch (e) {
    res.code = 401002;
    res.msg = 'delete failed';
  }
  return res;
};
/*上传子课程*/
async function addChildCourse(cc_id, cc_name, course_id, cc_src){
  let res = new ResData();
  let sql = `insert into childcourses(cc_id, cc_name, course_id, cc_src)
values (${cc_id},'${cc_name}',${course_id},'cc_src');`;
  console.log(sql);
  try{
    [rows] = await con.execute(sql);
  }catch(e){
    res.code = 401001;
    res.msg = 'insert failed';
  }
  return res;
}

module.exports = {
  getCourseByClaid,
  getCourseByPage,
  getChildcourseByCourseid,
  getCourseByCourseid,
  getCourseByUserid,
  delCourseByCourseid,
  getCourseid,
  addCourse,
  addCourseimg,
  delChildcourse,
  addChildCourse
};
